﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Configuration;

namespace Ebiz.libs
{
    public class DBUtilities
    {
        public enum ConnectionMode { 
            AuthWindows, AuthSQL
        }
        public enum QueryMode { Read, Write }
        public static bool CheckConnection(string strConnection) {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = strConnection;
            try{
                conn.Open();
                if (conn.State == ConnectionState.Open){
                    result = true;
                }else{
                    result = false;
                }
            }catch (SqlException ex){
                string ErrMsg = "Error: " + ex.ErrorCode.ToString();
                result = false;
            }finally{
                if (conn.State == ConnectionState.Open){
                    conn.Close();
                }
            }
            return result;
        }

        public static string SetConnectionString(Dictionary<string, object> inputText, ConnectionMode cnMode){
            string result = "";
            foreach (KeyValuePair<string, object> str in inputText){
                StringBuilder sbStr = new StringBuilder();
                sbStr.Append("Data Source=");
                sbStr.Append(inputText["ServerName"]);
                sbStr.Append(";Initial Catalog=");
                sbStr.Append(inputText["DatabaseName"]);
                if (cnMode == ConnectionMode.AuthWindows) {
                    sbStr.Append(";Integrated Security=True");
                }else if (cnMode == ConnectionMode.AuthSQL) {
                    sbStr.Append(";Persist Security Info=True");
                    sbStr.Append(";User ID=");
                    sbStr.Append(inputText["UserName"]);
                    sbStr.Append(";Password=");
                    sbStr.Append(inputText["Password"]);
                }
                result = sbStr.ToString();
            }
            return result;
        }

        public static string strConnection(string ConnectionName) {
            string result = "";
            //result = ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;
            ConnectionStringSettings dbCnSetting = ConfigurationManager.ConnectionStrings[ConnectionName];
            result = dbCnSetting.ConnectionString;
            return result;
        }

        public static string GetDBName(string ConnectionName) {
            SqlConnectionStringBuilder cnSB = new SqlConnectionStringBuilder(strConnection(ConnectionName));
            return cnSB.InitialCatalog;
        }

        public static Dictionary<string, object> ExecQuery(string query, QueryMode QryMode) {
            Dictionary<string, object> result = new Dictionary<string,object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    switch (QryMode) { 
                        case QueryMode.Read:
                            break;
                        case QueryMode.Write:
                            int exec = cmd.ExecuteNonQuery();
                            result.Add("Status", exec);
                            result.Add("Content", "Update Data Success");
                            break;
                    }
                    
                }catch (Exception e){
                    string errMsg = e.Message;
                    result.Add("Status", 0);
                    result.Add("Content", errMsg);
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        public static Dictionary<string, object> ExecQuery(string query, 
            Dictionary<string, object> Params, QueryMode QryMode){

            Dictionary<string, object> result = new Dictionary<string, object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    List<string> lstFieldName = new List<string>();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                        lstFieldName.Add(kvp.Key);
                    }
                    switch (QryMode) { 
                        case QueryMode.Read:
                            SqlDataReader rdr = cmd.ExecuteReader();
                            if (rdr.HasRows) {
                                List<object> lstReader = new List<object>();
                                while (rdr.Read()) {
                                    Dictionary<string, object> dctReader = new Dictionary<string, object>();
                                    foreach (string flName in lstFieldName) {
                                        object dbValue = rdr.GetOrdinal(flName);
                                        dctReader.Add(flName, dbValue);
                                    }
                                    lstReader.Add(dctReader);
                                }
                                result.Add("Status", 1);
                                result.Add("Content", lstReader);
                            }
                            break;
                        case QueryMode.Write:
                            int exec = cmd.ExecuteNonQuery();
                            result.Add("Status", exec);
                            result.Add("Content", "Update Data Success");
                            break;
                    }
                }catch (Exception e){
                    string errMsg = e.Message;
                    result.Add("Status", 0);
                    result.Add("Content", errMsg);
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
    }
}
